package top.callname.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.log4j.Logger;

import top.callname.bean.StudentBean;
import top.callname.bean.StudentPageList;


public class StudentDao extends BaseDao{

	
	Logger logger = Logger.getLogger(StudentDao.class);
	
	/**
	 * 删除整个班级的学生
	 * @param cid
	 * @return boolean
	 */
	public boolean deleteByCid(Integer cid){
		String sql = "DELETE FROM t_student WHERE cid="+cid;
		logger.info("删除学生信息："+sql);
		return adu(sql);
	}
	
	public boolean deleteBySid(Integer sid){
		String sql = "DELETE FROM t_student WHERE sid="+sid;
		logger.info("删除学生信息："+sql);
		return adu(sql);
	}

	public boolean insert(StudentBean student){
		String sql = "INSERT INTO t_student (stu_no, stu_name, cid, createtime) "
				+ "VALUES ( '"+student.getStuNo()+"', "
									+ "'"+student.getStuName()+"',"
									+ " '"+student.getCid()+"', "
									+ "NOW());";
		logger.info("新增学生信息："+sql);
		return adu(sql);
	}
	
	
	public StudentBean selectByOpenid(String openid){
		StudentBean bean = null;
		String sql =  "select  sid,cid,stu_no,stu_name,openid,createtime"
				+ "  from t_student where  openid='"+openid+"'";
		logger.info("根据openid查询学生信息："+sql);
		res = query(sql);
		try {
			if (res.next()){
				bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		return bean;
	}
	
	public StudentBean selectByStuNo(String stuNo){
		StudentBean bean = null;
		String sql =  "select  sid,cid,stu_no,stu_name,openid,createtime"
				+ "  from t_student where stu_no='"+stuNo+"'";
		logger.info("根据学号查询学生信息："+sql);
		res = query(sql);
		try {
			if (res.next()){
				bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		return bean;
	}
	
	public StudentBean selectBySid(Integer sid){
		StudentBean bean = null;
		String sql =  "select  sid,cid,stu_no,stu_name,openid,createtime"
				+ "  from t_student where sid="+sid;
		logger.info("根据sid查询学生信息："+sql);
		res = query(StringEscapeUtils.escapeSql(sql));
		try {
			if (res.next()){
				bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		return bean;
	}
	
	/**
	 * 更新信息
	 * @param stuNo
	 * @param openid
	 * @return boolean
	 */
	public boolean updateOpenid(String stuNo , String openid ){
		String sql = "update t_student set openid='"+openid+"' where stu_no='"+stuNo+"'";
		logger.info("更新学生微信openid信息："+sql);
		return adu(sql);
	}
	
	public boolean updateOpenid(Integer sid){
		String sql = "update t_student set openid='0' where sid="+sid;
		logger.info("更新学生微信openid信息："+sql);
		return adu(sql);
	}
	
	/**
	 * 
	 * Description:根据班级id分页查询
	 * @param pid
	 * @return List<ClassBean>
	 */
	public StudentPageList selectPageByCid(int pageNow , Integer cid){
		StudentPageList pageList = new StudentPageList();
		pageList.setPageNow(pageNow);
		//查询总记录
		int totalSize = getCount("t_student"," where cid="+cid);
		pageList.setTotalSize(totalSize);
		
		ArrayList<StudentBean> list = new ArrayList<StudentBean>();
		
		String limit = "order by sid desc limit "+pageList.getStart()+","+pageList.getOffset();
		String sql =  "select  a.sid,a.cid,a.stu_no,a.stu_name,a.openid,a.createtime,b.class_name from "
				+ "t_student a INNER JOIN  t_class b ON a.cid=b.cid AND a.cid="+cid+" "+limit;
		
		logger.info("分页查询学生信息："+sql);
		res = query(StringEscapeUtils.escapeSql(sql));
		try {
			while(res.next()){
				StudentBean bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				bean.setClassName(res.getString("class_name"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
				list.add(bean);
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		logger.debug("查询 t_student :"+list.toString());
		pageList.setList(list);
		return pageList;
	}
	
	
	/**
	 * 
	 * Description:分页查询全部
	 * @param pid
	 * @return List<ClassBean>
	 */
	public StudentPageList selectPage(int pageNow){
		StudentPageList pageList = new StudentPageList();
		pageList.setPageNow(pageNow);
		//查询总记录
		int totalSize = getCount("t_student");
		pageList.setTotalSize(totalSize);
		
		ArrayList<StudentBean> list = new ArrayList<StudentBean>();
		
		String limit = "order by sid desc limit "+pageList.getStart()+","+pageList.getOffset();
		String sql =  "select  a.sid,a.cid,a.stu_no,a.stu_name,a.openid,a.createtime,b.class_name from "
				+ "t_student a INNER JOIN  t_class b ON a.cid=b.cid  "+limit;
		
		logger.info("分页查询学生信息："+sql);
		res = query(StringEscapeUtils.escapeSql(sql));
		try {
			while(res.next()){
				StudentBean bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				bean.setClassName(res.getString("class_name"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
				list.add(bean);
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		pageList.setList(list);
		return pageList;
	}
	
	/**
	 * 根据班级查询所有已绑定微信的学生
	 * @param cids
	 * @return List<StudentBean>
	 */
	public List<StudentBean> selectBindedStudentByCids(String cids){
		String sql = "select sid,cid,stu_no,stu_name,openid,createtime from t_student where cid in("+cids+") and openid!='0' ";
		List<StudentBean>  list = new ArrayList<StudentBean>();
		logger.info("根据班级号查询所有已绑定微信的学生："+sql);
		res = query(sql);
		try {
			while(res.next()){
				StudentBean bean = new StudentBean();
				bean.setSid(res.getInt("sid"));
				bean.setCid(res.getInt("cid"));
				bean.setStuName(res.getString("stu_name"));
				bean.setStuNo(res.getString("stu_no"));
				bean.setOpenid(res.getString("openid"));
				String date = res.getString("createtime");
				bean.setCreatetime(date.substring(0,date.length()-2));
				list.add(bean);
			}
			closeDB();
		} catch (SQLException e) {
			logger.error("查询 t_student 出错："+e);
		}
		return list;
	}
	
	
	
}
